﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ecs
{
    public partial class Form3 : Form
    {
        public Form3()
        {
            InitializeComponent();
        }

        private void Form3_Load(object sender, EventArgs e)
        {
            // TODO: 这行代码将数据加载到表“bigdataDataSet1.huiyuan”中。您可以根据需要移动或移除它。
            this.huiyuanTableAdapter.Fill(this.bigdataDataSet1.huiyuan);

        }

        private async void button1_Click(object sender, EventArgs e)
        {
            // 输入验证
            if (string.IsNullOrWhiteSpace(textBox1.Text))
            {
                MessageBox.Show("请输入有效的店铺编码查询内容！",
                              "输入提示",
                              MessageBoxButtons.OK,
                              MessageBoxIcon.Information);
                return;
            }

            try
            {
                // 使用参数化查询防止SQL注入
                const string query = @"
            SELECT * 
            FROM huiyuan 
            WHERE 所属店铺编码 LIKE @SearchTerm
            ORDER BY 所属店铺编码";

                // 使用配置文件中的连接字符串（推荐）
                var connectionString = "Data Source=UNIX-OS;Initial Catalog=bigdata;" +
                                      "Integrated Security=True;Encrypt=True;" +
                                      "TrustServerCertificate=True";

                // 使用using自动释放资源
                using (var conn = new SqlConnection(connectionString))
                using (var cmd = new SqlCommand(query, conn))
                {
                    // 添加参数化查询
                    cmd.Parameters.Add("@SearchTerm", SqlDbType.NVarChar, 50).Value = $"%{textBox1.Text.Trim()}%";

                    // 异步操作防止界面冻结
                    await conn.OpenAsync();

                    var dataTable = new DataTable("Members");

                    // 使用SqlDataReader提升性能
                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        dataTable.Load(reader);
                    }

                    // 显示结果
                    if (dataTable.Rows.Count > 0)
                    {
                        dataGridView1.DataSource = dataTable;
                        dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.DisplayedCells);
                    }
                    else
                    {
                        dataGridView1.DataSource = null;
                        MessageBox.Show("未找到匹配的店铺编码",
                                      "查询结果",
                                      MessageBoxButtons.OK,
                                      MessageBoxIcon.Warning);
                    }
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show($"数据库错误：{ex.Message}\n错误代码：{ex.Number}",
                              "数据库异常",
                              MessageBoxButtons.OK,
                              MessageBoxIcon.Error);
            }
            catch (Exception ex)
            {
                MessageBox.Show($"操作失败：{ex.Message}",
                              "系统异常",
                              MessageBoxButtons.OK,
                              MessageBoxIcon.Error);
            }
        }

        private async void button2_Click(object sender, EventArgs e)
        {
            // 输入验证（删除操作需要更严格的验证）
            if (string.IsNullOrWhiteSpace(textBox1.Text))
            {
                MessageBox.Show("请输入有效的店铺编码！",
                              "输入提示",
                              MessageBoxButtons.OK,
                              MessageBoxIcon.Information);
                return;
            }

            try
            {
                // 修改后的删除SQL语句（使用精确匹配）
                const string deleteQuery = @"
            DELETE FROM huiyuan 
            WHERE 所属店铺编码 = @CardNo";  // 移除了ORDER BY和模糊查询

                var connectionString = "Data Source=UNIX-OS;Initial Catalog=bigdata;" +
                                      "Integrated Security=True;Encrypt=True;" +
                                      "TrustServerCertificate=True";

                using (var conn = new SqlConnection(connectionString))
                using (var cmd = new SqlCommand(deleteQuery, conn))
                {
                    // 修改参数名称和类型（根据实际字段类型调整）
                    cmd.Parameters.Add("@CardNo", SqlDbType.NVarChar, 100).Value = textBox1.Text.Trim();

                    await conn.OpenAsync();

                    // 执行删除操作（返回受影响行数）
                    int rowsAffected = await cmd.ExecuteNonQueryAsync();

                    // 显示操作结果
                    if (rowsAffected > 0)
                    {
                        MessageBox.Show($"成功删除 {rowsAffected} 条店铺记录",
                                      "操作成功",
                                      MessageBoxButtons.OK,
                                      MessageBoxIcon.Information);

                        // 可选：刷新数据视图
                        // RefreshDataGridView(); 
                    }
                    else
                    {
                        MessageBox.Show("未找到匹配的店铺记录",
                                      "删除结果",
                                      MessageBoxButtons.OK,
                                      MessageBoxIcon.Warning);
                    }
                }
            }
            catch (SqlException ex)
            {
                // 特别处理外键约束等错误
                string errorMsg = ex.Number == 547
                    ? "存在关联数据，无法直接删除！"
                    : ex.Message;

                MessageBox.Show($"删除失败：{errorMsg}",
                              "数据库异常",
                              MessageBoxButtons.OK,
                              MessageBoxIcon.Error);
            }
            catch (Exception ex)
            {
                MessageBox.Show($"操作失败：{ex.Message}",
                              "系统异常",
                              MessageBoxButtons.OK,
                              MessageBoxIcon.Error);
            }
        }

        private async void button3_Click(object sender, EventArgs e)
        {
            try
            {
                // 修改后的查询SQL语句
                const string query = @"
            SELECT * 
            FROM huiyuan";  // 移除了WHERE条件和参数

                var connectionString = "Data Source=UNIX-OS;Initial Catalog=bigdata;" +
                                      "Integrated Security=True;Encrypt=True;" +
                                      "TrustServerCertificate=True";

                using (var conn = new SqlConnection(connectionString))
                using (var cmd = new SqlCommand(query, conn))
                {
                    // 移除了参数添加代码（因为不需要查询条件）

                    await conn.OpenAsync();

                    var dataTable = new DataTable("Clients");

                    // 恢复使用DataReader读取数据
                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        dataTable.Load(reader);
                    }

                    // 显示结果
                    if (dataTable.Rows.Count > 0)
                    {
                        dataGridView1.DataSource = dataTable;
                        dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.DisplayedCells);
                    }
                    else
                    {
                        dataGridView1.DataSource = null;
                        MessageBox.Show("客户端表无数据",
                                      "查询结果",
                                      MessageBoxButtons.OK,
                                      MessageBoxIcon.Warning);
                    }
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show($"数据库错误：{ex.Message}",
                              "数据库异常",
                              MessageBoxButtons.OK,
                              MessageBoxIcon.Error);
            }
            catch (Exception ex)
            {
                MessageBox.Show($"操作失败：{ex.Message}",
                              "系统异常",
                              MessageBoxButtons.OK,
                              MessageBoxIcon.Error);
            }
        }
    }
}
